package com.scfinance.SPD.dao;

import com.scfinance.SPD.util.PropsUtil;
import com.scfinance.SPD.util.Validator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by admin on 2017/8/7.
 */
@Repository
public class OrderDaoImpl {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> needAppId() {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        String sql = "SELECT\n" +
                "\tca.order_id\n" +
                "FROM\n" +
                "\tcustomer_app ca\n" +
                "INNER JOIN mini_status ms ON ca.order_id = ms.orderId\n" +
                "AND ms.type = - 1\n" +
                "AND ms.statusId = 1\n" +
                "AND ms.`value` = 1\n" +
                "INNER JOIN s_loan_order_enclosure loe ON loe.id = ca.order_id\n" +
                "AND (loe.SPD_app_no IS NULL OR loe.SPD_app_no = '')\n" +
                "INNER JOIN s_loan_order lo ON lo.id = ca.order_id\n" +
                "WHERE ca.app_type = 0";
        if (Validator.isNotNull(PropsUtil.getProperty("loanOrderId"))) {
            sql += " AND ca.order_id = '" + PropsUtil.getProperty("loanOrderId") + "' ";
        }
        sql += " ORDER BY ca.update_time LIMIT 0," + PropsUtil.getProperty("listSize");
        resultList = jdbcTemplate.queryForList(sql);
        return resultList;
    }

    public List<Map<String, Object>> getCustomerAppByOrderId(Object[] params) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        String sql = "SELECT\n" +
                "\tca.order_id,\n" +
                "\tlo.pi_id,\n" +
                "\tlo.status,\n" +
                "\tlo.status_name,\n" +
                "\tca.customer_name AS shenqingrenzhongwenminchen,\n" +
                "\tca.cardType AS productname,\n" +
                "\tcu.id_number AS shengqingrencardnumber,\n" +
                "\tca.IDLimitDate AS certificatelimit,\n" +
                "\tca.houseStatus AS famliyhousestatus,\n" +
                "\tca.familyAreaCode AS shenqignrenfamliyareacode,\n" +
                "\tca.familyPhone AS shenqingrenfamliytel,\n" +
                "\tca.mobile AS shenqingrenmobilenumber,\n" +
                "\tca.org_name AS gongsizhongwenname,\n" +
                "\tloe.province  AS famliyaddresszone1,\n" +
                "\tloe.city AS famliyaddresszone2,\n" +
                "\tloe.address AS famliyaddresszone3,\n" +
                "\tloe.SPD_app_no AS appId,\n" +
                "\tloe.relatives_phone AS zhixiqinshumobilephone,\n" +
                "\tloe.id_card_front,\n" +
                "\tloe.id_card_back,\n" +
                "\tloe.loanVoucher,\n" +
                "\tloe.pretrial,\n" +
                "\tloe.car_no,\n" +
                "\tcm.car_brand_name AS theproductbrand,\n" +
                "\tcm.car_series_name AS models,\n" +
                "\tcm.car_models_name AS modelsdetail,\n" +
                "\tca.relatives AS zhixiqinshuname,\n" +
                "\tca.relatives_name AS zhixiqinshurelation,\n" +
                "\tlod.car_amount,\n" +
                "\tlod.approved_amount AS loanApproved,\n" +
                "\tprolong.approved_amount AS prolongAA,\n" +
                "\tinsurance.approved_amount AS insuranceAA,\n" +
                "\tlod.periods AS qffenqiqishu,\n" +
                "\tlod.lending_rate AS qffenqifeilv,\n" +
                "\tpf.repayment_method AS qffenqitype,\n" +
                "\tspfr.prdNo,\n" +
                "\tspfr.insuranceNo,\n" +
                "\tsr.citycode,\n" +
                "\tca.occupation AS gongsizhiwu,\n" +
                "\tca.companyPhone AS gongsitel,\n" +
                "\tca.companyAreaCode AS gongsitelareacode,\n" +
                "\tca.workAge AS gongsiserviceyears,\n" +
                "\tca.companyZipCode AS gongsipostcode,\n" +
                "\tca.familyZipCode AS famliypostcode,\n" +
                "\tca.postType AS cardexpressaddress,\n" +
                "\tca.immedfamilyAreaCode AS zhixiqinshutelareacode,\n" +
                "\tca.immedfamilyPhone AS zhixiqinshutel,\n" +
                "\tca.hasChild AS qfishaschild,\n" +
                "\tca.otherBankCard AS tahangjiejika,\n" +
                "\tca.deductBankNo AS homecurrencywithholdnumber,\n" +
                "\tca.bankAgreementCode AS applicationagreementbarcode,\n" +
                "\tca.marriageFlag AS shenqingrenmaritalstatus,\n" +
                "\tca.educationFlag AS shenqingrenlevelofducation,\n" +
                "\tca.email AS shenqingrenemail,\n" +
                "\tca.inCome AS shenqingrenincome,\n" +
                "\tca.orgLevelOne AS gongsiaddressarea1,\n" +
                "\tca.orgLevelTwo AS gongsiaddressarea2,\n" +
                "\tca.orgAddress AS gongsiaddressarea3,\n" +
                "\tca.salesManCode AS popularizepepolenum,\n" +
                "\tca.applicationContract,\n" +
                "\tca.groupPhoto,\n" +
                "\tca.refereeCode,\n" +
                "\tca.refereeMobile,\n" +
                "\tca.refereeOrgCode,\n" +
                "\tca.wayOfUse AS qfgoucheyongtu,\n" +
                "\tspd.uploadType\n" +
                "FROM\n" +
                "\tcustomer_app ca\n" +
                "INNER JOIN customer cu ON ca.customer_id = cu.id\n" +
                "INNER JOIN s_loan_order_enclosure loe ON loe.id = ca.order_id AND loe.SPD_app_no IS NOT NULL AND loe.SPD_app_no != ''\n" +
                "INNER JOIN s_loan_order lo ON lo.id = ca.order_id\n" +
                "INNER JOIN s_loan_order_detail lod ON lo.id = lod.id\n" +
                "INNER JOIN s_product_finance pf ON pf.id = lo.product_id\n" +
                "INNER JOIN s_product_finance_rate spfr ON spfr.product_finance_id = pf.id AND spfr.due_time = lod.periods\n" +
                "INNER JOIN s_car_models cm ON cm.id = lo.car_models_id\n" +
                "LEFT JOIN (select sio1.loan_order_id,sio1.approved_amount from s_insurance_order sio1 INNER JOIN s_product_insurance spi1 ON spi1.id = sio1.product_insurance_id AND spi1.type = 1) prolong ON prolong.loan_order_id = lo.id\n" +
                "LEFT JOIN (select sio2.loan_order_id,sio2.approved_amount from s_insurance_order sio2 INNER JOIN s_product_insurance spi2 ON spi2.id = sio2.product_insurance_id AND spi2.type = 2) insurance ON insurance.loan_order_id = lo.id\n" +
                "INNER JOIN c_user u ON u.id = ca.recorderId\n" +
                "INNER JOIN s_region sr ON u.cityID = sr.id\n" +
                "INNER JOIN spdoutput spd ON spd.orderId = ca.order_id AND spd.uploadFlag + 0 >= 3\n" +
                "WHERE\n" +
                "\tca.app_type = 0  AND ca.order_id = ?";

        resultList = jdbcTemplate.queryForList(sql,params);
        return resultList;
    }

    public List<Map<String, Object>> getCustomerApp() {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        String sql = "SELECT\n" +
                "\tca.order_id,\n" +
                "\tlo.pi_id,\n" +
                "\tlo.status,\n" +
                "\tlo.status_name,\n" +
                "\tca.customer_name AS shenqingrenzhongwenminchen,\n" +
                "\tca.cardType AS productname,\n" +
                "\tcu.id_number AS shengqingrencardnumber,\n" +
                "\tca.IDLimitDate AS certificatelimit,\n" +
                "\tca.houseStatus AS famliyhousestatus,\n" +
                "\tca.familyAreaCode AS shenqignrenfamliyareacode,\n" +
                "\tca.familyPhone AS shenqingrenfamliytel,\n" +
                "\tca.mobile AS shenqingrenmobilenumber,\n" +
                "\tca.org_name AS gongsizhongwenname,\n" +
                "\tloe.province  AS famliyaddresszone1,\n" +
                "\tloe.city AS famliyaddresszone2,\n" +
                "\tloe.address AS famliyaddresszone3,\n" +
                "\tloe.SPD_app_no AS appId,\n" +
                "\tloe.relatives_phone AS zhixiqinshumobilephone,\n" +
                "\tloe.id_card_front,\n" +
                "\tloe.id_card_back,\n" +
                "\tloe.loanVoucher,\n" +
                "\tloe.pretrial,\n" +
                "\tloe.car_no,\n" +
                "\tcm.car_brand_name AS theproductbrand,\n" +
                "\tcm.car_series_name AS models,\n" +
                "\tcm.car_models_name AS modelsdetail,\n" +
                "\tca.relatives AS zhixiqinshuname,\n" +
                "\tca.relatives_name AS zhixiqinshurelation,\n" +
                "\tlod.car_amount,\n" +
                "\tlod.approved_amount AS loanApproved,\n" +
                "\tprolong.approved_amount AS prolongAA,\n" +
                "\tinsurance.approved_amount AS insuranceAA,\n" +
                "\tlod.periods AS qffenqiqishu,\n" +
                "\tlod.lending_rate AS qffenqifeilv,\n" +
                "\tpf.repayment_method AS qffenqitype,\n" +
                "\tspfr.prdNo,\n" +
                "\tspfr.insuranceNo,\n" +
                "\tsr.citycode,\n" +
                "\tca.occupation AS gongsizhiwu,\n" +
                "\tca.companyPhone AS gongsitel,\n" +
                "\tca.companyAreaCode AS gongsitelareacode,\n" +
                "\tca.workAge AS gongsiserviceyears,\n" +
                "\tca.companyZipCode AS gongsipostcode,\n" +
                "\tca.familyZipCode AS famliypostcode,\n" +
                "\tca.postType AS cardexpressaddress,\n" +
                "\tca.immedfamilyAreaCode AS zhixiqinshutelareacode,\n" +
                "\tca.immedfamilyPhone AS zhixiqinshutel,\n" +
                "\tca.hasChild AS qfishaschild,\n" +
                "\tca.otherBankCard AS tahangjiejika,\n" +
                "\tca.deductBankNo AS homecurrencywithholdnumber,\n" +
                "\tca.bankAgreementCode AS applicationagreementbarcode,\n" +
                "\tca.marriageFlag AS shenqingrenmaritalstatus,\n" +
                "\tca.educationFlag AS shenqingrenlevelofducation,\n" +
                "\tca.email AS shenqingrenemail,\n" +
                "\tca.inCome AS shenqingrenincome,\n" +
                "\tca.orgLevelOne AS gongsiaddressarea1,\n" +
                "\tca.orgLevelTwo AS gongsiaddressarea2,\n" +
                "\tca.orgAddress AS gongsiaddressarea3,\n" +
                "\tca.salesManCode AS popularizepepolenum,\n" +
                "\tca.applicationContract,\n" +
                "\tca.groupPhoto,\n" +
                "\tca.refereeCode,\n" +
                "\tca.refereeMobile,\n" +
                "\tca.refereeOrgCode,\n" +
                "\tca.wayOfUse AS qfgoucheyongtu,\n" +
                "\tspd.uploadType\n" +
                "FROM\n" +
                "\tcustomer_app ca\n" +
                "INNER JOIN customer cu ON ca.customer_id = cu.id\n" +
                "INNER JOIN s_loan_order_enclosure loe ON loe.id = ca.order_id AND loe.SPD_app_no IS NOT NULL AND loe.SPD_app_no != ''\n" +
                "INNER JOIN s_loan_order lo ON lo.id = ca.order_id\n" +
                "INNER JOIN s_loan_order_detail lod ON lo.id = lod.id\n" +
                "INNER JOIN s_product_finance pf ON pf.id = lo.product_id\n" +
                "INNER JOIN s_product_finance_rate spfr ON spfr.product_finance_id = pf.id AND spfr.due_time = lod.periods\n" +
                "INNER JOIN s_car_models cm ON cm.id = lo.car_models_id\n" +
                "LEFT JOIN (select sio1.loan_order_id,sio1.approved_amount from s_insurance_order sio1 INNER JOIN s_product_insurance spi1 ON spi1.id = sio1.product_insurance_id AND spi1.type = 1) prolong ON prolong.loan_order_id = lo.id\n" +
                "LEFT JOIN (select sio2.loan_order_id,sio2.approved_amount from s_insurance_order sio2 INNER JOIN s_product_insurance spi2 ON spi2.id = sio2.product_insurance_id AND spi2.type = 2) insurance ON insurance.loan_order_id = lo.id\n" +
                "INNER JOIN c_user u ON u.id = ca.recorderId\n" +
                "INNER JOIN s_region sr ON u.cityID = sr.id\n" +
                "INNER JOIN spdoutput spd ON spd.orderId = ca.order_id AND spd.uploadFlag + 0 >= 3\n" +
                "WHERE\n" +
                "\tca.app_type = 0 ";
        if (Validator.isNotNull(PropsUtil.getProperty("loanOrderId"))) {
            sql += " AND ca.order_id = '" + PropsUtil.getProperty("loanOrderId") + "' ";
        }
        sql += " ORDER BY ca.update_time LIMIT 0," + PropsUtil.getProperty("listSize");
        resultList = jdbcTemplate.queryForList(sql);
        return resultList;
    }

    public Map findMiniStatus(Object[] params) {
        String sql = "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\tmini_status\n" +
                "WHERE\n" +
                "\ttype = - 1\n" +
                "AND statusId = 1\n" +
                "AND orderId = ?";
        Map resultMap = jdbcTemplate.queryForMap(sql, params);
        return resultMap;
    }

    public Boolean updateSingleFlag(Object[] params) {
        Boolean returnFlag = true;
        String sql = "UPDATE mini_status\n" +
                "SET `value` = ?, updateTime = NOW()\n" +
                "WHERE\n" +
                "\ttype = - 1\n" +
                "AND statusId = 1\n" +
                "AND orderId = ?";
        int flag = jdbcTemplate.update(sql, params);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;

    }

    public Boolean saveAppID(String appId, String loanOrderId) {
        Boolean returnFlag = true;
        Object[] params = new Object[2];
        params[0] = appId;
        params[1] = loanOrderId;
        String sql = "UPDATE s_loan_order_enclosure\n" +
                "SET SPD_app_no = ?\n" +
                "WHERE\n" +
                "\tid = ?";
        int flag = jdbcTemplate.update(sql, params);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;
    }

    public Boolean saveOperationRecord(Object[] recordParams) {
        Boolean returnFlag = true;
        String sql = "INSERT INTO s_operation_record (\n" +
                "\tid,\n" +
                "\tpi_id,\n" +
                "\tstatus_id,\n" +
                "\tstatus_name,\n" +
                "\ttransaction_id,\n" +
                "\ttransaction_name,\n" +
                "\tcreate_by,\n" +
                "\tcreate_by_name,\n" +
                "\tcreate_time,\n" +
                "\tupdate_time\n" +
                ")\n" +
                "VALUES\n" +
                "\t(?,?,?,?,?,?,?,?,NOW(),NOW())";
        int flag = jdbcTemplate.update(sql, recordParams);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;
    }

    public Boolean saveSPDReturn(Object[] paramsObj) {
        Boolean returnFlag;
        String sql = "INSERT INTO spdoutput (\n" +
                "\torderId,\n" +
                "\tcustomerName,\n" +
                "\tuploadCode,\n" +
                "\tuploadMsg,\n" +
                "\tSPDappId,\n" +
                "\tcreate_time,\n" +
                "\tupdate_time\n" +
                ")\n" +
                "VALUES\n" +
                "\t(?,?,?,?,?, NOW(), NOW())";
        int flag = jdbcTemplate.update(sql, paramsObj);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;
    }

    public Map findSPDReturn(String loanOrderId) {
        Object[] params = new Object[1];
        params[0] = loanOrderId;
        String sql = "SELECT\n" +
                "\tcount(1) AS num\n" +
                "FROM\n" +
                "\tspdoutput\n" +
                "WHERE\n" +
                "\torderId = ?";
        Map resultMap = jdbcTemplate.queryForMap(sql, params);
        return resultMap;
    }

    public Boolean updateSPDReturn(Object[] paramsObj) {
        Boolean returnFlag;
        String sql = "UPDATE spdoutput\n" +
                "SET customerName = ?, uploadCode = ?, uploadMsg = ?, SPDappId = ?, update_time = NOW()\n" +
                "WHERE\n" +
                "\torderId = ?";
        int flag = jdbcTemplate.update(sql, paramsObj);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;
    }

    public Boolean updateSPDreturn(Object[] stayPatchParams) {
        Boolean returnFlag = true;
        String sql = "UPDATE spdoutput\n" +
                "SET stayPatchCode = ?, stayPatchMsg = ?, uploadDate = ?, update_time = NOW()\n" +
                "WHERE\n" +
                "\tSPDappId = ?";
        int flag = jdbcTemplate.update(sql, stayPatchParams);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;
    }

    public Map getSingleSPDReturn(Object[] params) {
        Map resultMap = null;
        String sql = "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\tspdoutput\n" +
                "WHERE\n" +
                "\tSPDappId = ?";
        if (jdbcTemplate.queryForList(sql, params).size()>0){
            resultMap = jdbcTemplate.queryForMap(sql, params);
        }
        return resultMap;
    }

    public Map pushSPDEntry(Object[] params) {
        String sql = "SELECT\n" +
                "\tca.customer_name AS customerName,\n" +
                "\tspd.create_time AS time,\n" +
                "\tspd.SPDappId AS AppId,\n" +
                "\tca.recorderId AS userId,\n" +
                "\tca.order_id AS orderId\n" +
                "FROM\n" +
                "\tcustomer_app ca\n" +
                "INNER JOIN spdoutput spd ON ca.order_id = spd.orderId\n" +
                "WHERE\n" +
                "\tca.app_type = 0\n" +
                "AND ca.active = 1\n" +
                "AND ca.order_id = ?";
        Map resultMap = jdbcTemplate.queryForMap(sql, params);
        return resultMap;
    }

    public Boolean setSPDOutputFlag(Object[] params) {
        Boolean returnFlag;
        String sql = "UPDATE spdoutput\n" +
                "SET uploadFlag = ?\n" +
                "WHERE\n" +
                "\torderId = ?";
        int flag = jdbcTemplate.update(sql, params);
        if (flag > 0) {
            returnFlag = true;
        } else {
            returnFlag = false;
        }
        return returnFlag;
    }
}
